package com.bailugansan.efficiencyboard.dao.impl;

import com.alibaba.fastjson.JSON;
import com.bailugansan.efficiencyboard.dao.PoolDao;
import com.bailugansan.efficiencyboard.pojo.Pool;
import com.bailugansan.efficiencyboard.utils.DateUtils;
import com.bailugansan.efficiencyboard.utils.Druid;
import com.bailugansan.efficiencyboard.vo.BoardVo;
import com.bailugansan.efficiencyboard.vo.PoolVo;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.io.FileUtils;
import org.springframework.stereotype.Repository;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author BailuGansan
 */

@Repository
public class PoolDaoImpl implements PoolDao {

    @Override
    public boolean addPool(String poolName, String color, String uid) {
        QueryRunner qr = new QueryRunner();
        Connection conn = Druid.getConnection();
        String sql = "insert into pool (pool_name, color, user_id) values (?, ?, ?)";

        try { // update添加操作成功才会向下执行，失败返回false
            qr.update(conn, sql, poolName, color, uid);
            return true;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return false;
    }

    @Override
    public boolean updatePool(int poolId, String poolName, String color, String uid) {
        QueryRunner qr = new QueryRunner();
        Connection conn = Druid.getConnection();
        String sql = "update pool set pool_name = ?, color = ? where pool_id = ? and user_id = ?";

        try {
            qr.update(conn, sql, poolName, color, poolId, uid);
            return true;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return false;
    }

    @Override
    public boolean removerPool(int poolId, String uid) {
        QueryRunner qr = new QueryRunner();
        Connection conn = Druid.getConnection();
        String sql = "delete from pool where pool_id = ? and user_id = ? and pool_name not in(?, ?, ?, ?, ?, ?)";

        try {
            qr.update(conn, sql, poolId, uid, "任务计划池", "任务就绪池", "任务执行池", "任务验收池", "任务完成池", "任务过期池");
            return true;
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return false;
    }

    @Override
    public boolean initPool(String userName) {
        QueryRunner qr = new QueryRunner();
        Connection conn = Druid.getConnection();
        String uid = getUidByUserName(userName);
        String sql = "insert into pool (pool_name, color, user_id) values(?, ?, ?)";
        String initBoardsSql = "insert into taskboard(pid, task_title, `order`, priority, start_year, start_time, dead_line_year, dead_line_time, descript) values(?, ?, ?, ?, ?, ?, ?, ?, ?)";
        // 读取初始化池子json文件
        File file = new File("src/main/resources/init.json");
        try {
            // org.apache.commons.io.FileUtils
            // FileUtils.readFileToString(file) 已过时
            String jsonStr = FileUtils.readFileToString(file, "utf-8");

            // com.alibaba.fastjson.JSONException: syntax error, expect {, actual [, pos 0, fastjson-version 1.2.47
            // PoolVo poolVo = JSON.parseObject(s, PoolVo.class);
            // 报错的原因: init.json 的最外层被[]包裹起来，json.parseObject()无法解析
            // 解决方法: 使用 json.parseArray()
            // 将json转换为视图
            List<PoolVo> poolVos = JSON.parseArray(jsonStr, PoolVo.class);
            Object[][] poolVosParams = new Object[poolVos.size()][];
            for(int i = 0; i< poolVos.size(); i++){
                PoolVo p = poolVos.get(i);
                poolVosParams[i] = new Object[3];
                poolVosParams[i][0] = p.getPoolName();
                poolVosParams[i][1] = p.getColor();
                poolVosParams[i][2] = uid;
            }

            qr.batch(conn, sql, poolVosParams);

            // 1. 遍历每个pool的任务
            // 2. 根据每个pool，从数据库中查询出对应的poolId，更新board里面的pId
            List<BoardVo> boardVos = new ArrayList<>();
            for (PoolVo p: poolVos){
                List<BoardVo> boards = p.getBoards();
                // 遍历每个pool取出池子名
                String poolName = p.getPoolName();
                // 根据池子名和uid获取pId
                int pId = getPoolId(poolName, uid);
                for(BoardVo b: boards){
                    // 更新
                    b.setPId(pId);
                    // 添加进入List集合中
                    boardVos.add(b);
                }
            }

            Object[][] BoardVosParams = new Object[boardVos.size()][];
            for(int i = 0; i< boardVos.size(); i++){
                BoardVo b = boardVos.get(i);
                BoardVosParams[i] = new Object[9];
                BoardVosParams[i][0] = b.getPId();
                BoardVosParams[i][1] = b.getTaskTitle();
                BoardVosParams[i][2] = b.getOrder();
                BoardVosParams[i][3] = b.getPriority();
                BoardVosParams[i][4] = b.getStartYear();
                // 数据库中存储的Time字段类型为varchar，此处将Time字段转换为string类型
                BoardVosParams[i][5] = DateUtils.long2String(b.getStartTime());
                BoardVosParams[i][6] = b.getDeadLineYear();
                BoardVosParams[i][7] = DateUtils.long2String(b.getDeadLineTime());
                BoardVosParams[i][8] = b.getDescript();
            }

            qr.batch(conn, initBoardsSql, BoardVosParams);
            return true;
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException s){
            s.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return false;
    }

    public String getUidByUserName(String userName){
        QueryRunner qr = new QueryRunner();
        Connection conn = Druid.getConnection();
        String sql = "select id from tb_users where user_name = ?";

        try {
//            ColumnListHandler pId = new ColumnListHandler();
            // java.lang.ClassCastException: java.base/java.util.ArrayList cannot be cast to java.base/java.lang.Integer
            // Integer属于不可更改类型，而且Long和Integer没有任何继承关系，因此不能这样转换
            Object query = qr.query(conn, sql, new ScalarHandler<>(), userName);
            return query.toString();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return null;
    }

    @Override
    public List<Pool> getPools(String uid) {
        QueryRunner qr = new QueryRunner();
        Connection conn = Druid.getConnection();
        String getPoolsSql = "select * from pool where user_id = ?";

        try {
            List<Pool> pools = qr.query(conn, getPoolsSql, new BeanListHandler<>(Pool.class), uid);
            return pools;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 开发时间有限，虽然这种方式会非常消耗性能，但没有办法
     * @param poolName
     * @param uid
     * @return
     */
    @Override
    public int getPoolId(String poolName, String uid){
        QueryRunner qr = new QueryRunner();
        Connection conn = Druid.getConnection();
        String sql = "select pool_id from pool where pool_name = ? and user_id = ?";

        try {
//            ColumnListHandler pId = new ColumnListHandler();
            // java.lang.ClassCastException: java.base/java.util.ArrayList cannot be cast to java.base/java.lang.Integer
            // Integer属于不可更改类型，而且Long和Integer没有任何继承关系，因此不能这样转换
            int query = qr.query(conn, sql, new ScalarHandler<>(), poolName, uid);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return -1;
    }
}
